/*
 * Aipo is a groupware program developed by Aimluck,Inc.
 * Copyright (C) 2004-2011 Aimluck,Inc.
 * http://www.aipo.com
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package com.nayuta.aipo.dao;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.TimeZone;

import com.nayuta.aipo.dao.JDBCUtils.ConnectionBean;


/**
 * スケジュールのユーティリティクラスです。
 * 
 */
public class ScheduleUtils {

  /** <code>SCHEDULEMAP_TYPE_USER</code> ユーザ */
  public static final String SCHEDULEMAP_TYPE_USER = "U";

  /** <code>SCHEDULEMAP_TYPE_FACILITY</code> 設備 */
  public static final String SCHEDULEMAP_TYPE_FACILITY = "F";

  /** <code>TARGET_FACILITY_ID</code> ユーザによる表示切り替え用変数の識別子 */
  public static final String TARGET_FACILITY_ID = "f";

  public static final String MAIL_FOR_INSERT = "I";

  public static final String MAIL_FOR_UPDATE = "U";

  public static final String MAIL_FOR_DELETE = "D";

  /** すべての人にメール送信 */
  public static final String MAIL_FOR_ALL = "A";

  /** すべての人にメール送信しない */
  public static final String MAIL_FOR_NONE = "N";

  public static final String FLAG_CHANGE_TURN_STR = "flagchangeturn";

  public static final String SCHEDULE_PORTLET_NAME = "Schedule";

  public static final String AJAX_SCHEDULE_PORTLET_NAME = "AjaxSchedule";

  public static final String SCHEDULE_ADMIN_PORTLET_NAME = "ScheduleAdmin";

  /** 検索キーワード変数の識別子 */
  public static final String TARGET_KEYWORD = "keyword";

  public static ScheduleBean getScheduleList(int userId,
	      Date viewStart, Date viewEnd, List<Integer> users,
	      List<Integer> facilities) {
	    return getScheduleList(
	      userId,
	      viewStart,
	      viewEnd,
	      users,
	      facilities,
	      null,
	      -1,
	      -1,
	      false);
	  }
  
  public static ScheduleBean getScheduleList(int userId,
      Date viewStart, Date viewEnd, List<Integer> users,
      List<Integer> facilities, String keyword, int page, int limit,
      boolean isSearch) {

    StringBuilder select = new StringBuilder();

    select.append("select");
    if (!isSearch) {
      select.append(" eip_t_schedule_map.id, ");
      select.append(" eip_t_schedule_map.user_id,");
      select.append(" eip_t_schedule_map.status,");
      select.append(" eip_t_schedule_map.type,");
    } else {
      select.append(" distinct");
    }
    select.append(" eip_t_schedule.schedule_id,");
    select.append(" eip_t_schedule_map.common_category_id,");
    select.append(" eip_t_schedule.owner_id,");
    select.append(" eip_t_schedule.parent_id,");
    select.append(" eip_t_schedule.name,");
    select.append(" eip_t_schedule.place,");
    select.append(" eip_t_schedule.note,");
    select.append(" eip_t_schedule.start_date,");
    select.append(" eip_t_schedule.end_date,");
    select.append(" eip_t_schedule.update_date,");
    select.append(" eip_t_schedule.public_flag,");
    select.append(" eip_t_schedule.repeat_pattern,");
    select.append(" eip_t_schedule.create_user_id,");
    if (isSearch) {
      select.append(" eip_t_schedule.note,");
      select.append(" eip_t_schedule_map.user_id,");
      select.append(" eip_t_schedule_map.type,");
    }
    select.append(" eip_t_schedule.edit_flag,");
    select
      .append(" (SELECT COUNT(*) FROM eip_t_schedule_map t0 WHERE (t0.schedule_id = eip_t_schedule_map.schedule_id) AND (t0.user_id = ").append(String.valueOf(userId)).append(")) AS is_member,");
    select
      .append(" (SELECT COUNT(*) FROM eip_t_schedule_map t1 WHERE (t1.schedule_id = eip_t_schedule_map.schedule_id) AND (t1.status <> 'R') AND (t1.type = 'F')) AS f_count,");
    select
      .append(" (SELECT COUNT(*) FROM eip_t_schedule_map t2 WHERE (t2.schedule_id = eip_t_schedule_map.schedule_id) AND (t2.status <> 'R') AND (t2.type <> 'F')) AS u_count");

    StringBuilder count = new StringBuilder();
    count.append("select count(distinct eip_t_schedule.schedule_id) AS c ");

    StringBuilder body = new StringBuilder();
    body
      .append(" FROM eip_t_schedule_map, eip_t_schedule WHERE eip_t_schedule_map.schedule_id = eip_t_schedule.schedule_id");
    boolean hasKeyword = false;
    if (keyword != null && keyword.length() > 0) {
      hasKeyword = true;
      body.append(" AND (");
      body
        .append(" eip_t_schedule.name LIKE #bind($keyword) OR eip_t_schedule.note LIKE #bind($keyword) OR eip_t_schedule.place LIKE #bind($keyword) ");
      body.append(" ) ");

    }

    if ((users != null && users.size() > 0)
      || (facilities != null && facilities.size() > 0)) {
      body.append(" AND (");
    }

    if (users != null && users.size() > 0) {
      body.append(" ((eip_t_schedule_map.type = 'U')");
      body.append(" AND (eip_t_schedule_map.user_id IN (");

      boolean isFirst = true;
      for (Integer num : users) {
        if (!isFirst) {
          body.append(",");

        }
        body.append(num.intValue());
        isFirst = false;
      }
      body.append(")))");
    }

    if (facilities != null && facilities.size() > 0) {
      if (users != null && users.size() > 0) {
        body.append(" OR ");
      }
      body.append(" ((eip_t_schedule_map.type = 'F')");
      body.append(" AND (eip_t_schedule_map.user_id IN (");

      boolean isFirst = true;
      for (Integer num : facilities) {
        if (!isFirst) {
          body.append(",");

        }
        body.append(num.intValue());
        isFirst = false;
      }
      body.append(")))");
    }

    if ((users != null && users.size() > 0)
      || (facilities != null && facilities.size() > 0)) {
      body.append(")");
    }

    if (viewStart != null && viewEnd != null) {
      body.append(" AND (((eip_t_schedule.end_date >= '");
      body
        .append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(viewStart));
      body.append("') AND (eip_t_schedule.start_date <= '");
      body.append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(viewEnd));

      body
        .append("')) OR ((eip_t_schedule.repeat_pattern <> 'N') AND (eip_t_schedule.repeat_pattern <> 'S')))");
    } else {
      if (isSearch) {
        body.append(" AND (eip_t_schedule.public_flag = 'O'");

      } else {
        body.append(" AND (eip_t_schedule.public_flag <> 'P'");
      }
      body
        .append(" OR ((SELECT COUNT(*) FROM eip_t_schedule_map t0 WHERE (t0.schedule_id = eip_t_schedule_map.schedule_id) AND (t0.user_id = ").append(String.valueOf(userId)).append(")) > 0 )");
      body
        .append(" OR (eip_t_schedule_map.type = 'U' AND eip_t_schedule_map.user_id = ").append(String.valueOf(userId)).append(")");
      body.append(" OR ( eip_t_schedule.owner_id = ").append(String.valueOf(userId)).append("))");
      body.append(" AND (eip_t_schedule_map.status <> 'D')");
    }

    StringBuilder last = new StringBuilder();

    last
      .append(" ORDER BY eip_t_schedule.start_date, eip_t_schedule.end_date, eip_t_schedule_map.type,eip_t_schedule.update_date,eip_t_schedule_map.user_id");

    ScheduleBean ret = new ScheduleUtils.ScheduleBean();
    ret.setConnectionBean(JDBCUtils.querySql(select.toString() + body.toString() + last.toString()));
    ret.setSql(select.toString() + body.toString() + last.toString());
    return ret;
  }
  
  public static class ScheduleBean {
	  private ConnectionBean connectionBean;
	  private String sql;
	public ConnectionBean getConnectionBean() {
		return connectionBean;
	}
	public void setConnectionBean(ConnectionBean connectionBean) {
		this.connectionBean = connectionBean;
	}
	public String getSql() {
		return sql;
	}
	public void setSql(String sql) {
		this.sql = sql;
	}
  }
}
